﻿CREATE PROCEDURE [dbo].[proc_Orders_Department]
(
	@CompanyId int,
	@DepartmentId int,
	@MainProjectId int,
	@Stext nvarchar(50),
	@StartDate varchar(20),
	@EndDate varchar (20),
	@StartIndex int,
	@EndIndex int
)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	;WITH List As (Select ROW_NUMBER() OVER (Order By CreateDate Desc) AS Row,
		Id,OId,CusName,DepId,
		Salesman,
		IsNull(YingShouPrice,0) As YingShouPrice,
		IsNull(ShiShouPrice,0) As ShiShouPrice,
		CreateDate,StateId,
		IsNull(FinanceRenlingDate,'1900-01-01') As FinanceRenlingDate
		From [Order] a
		Where CompanyId=@CompanyId And CreateDate>=@sRq And CreateDate<=@eRq
			And DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)
			And OId In(Select Distinct OId From [Order_item]
			Where CompanyId=@CompanyId And CreateDate>=@sRq And (@MainProjectId=0 Or ProductId=@MainProjectId)
		)
		And  StateId>0
		And (@SText='' Or CusName Like '%'+@SText+'%' Or Salesman Like '%'+@SText+'%')
	)
	Select *,
	(Select Top 1 d.DName From department d where d.id=List.DepId) As DepName,
	(Select Count(0) From list) As RecordCount
	From List Where Row Between @StartIndex And @EndIndex
End
